<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns='http://www.w3.org/1999/xhtml' xml:lang='en'>
<head>
  <meta content="text/html; charset=ISO-8859-1"  http-equiv="content-type" />
  <link rel="stylesheet" type="text/css" href="../style.css" />
  <title>SOCI - PostgreSQL Backend Reference</title>
</head>

<body>
<p class="banner">SOCI - The C++ Database Access Library</p>

<h2>PostgreSQL Backend Reference</h2>

<div class="navigation">
<a href="#prerequisites">Prerequisites</a><br />
<div class="navigation-indented">
  <a href="#versions">Supported Versions</a><br />
  <a href="#tested">Tested Platforms</a><br />
  <a href="#required">Required Client Libraries</a><br />
</div>
<a href="#connecting">Connecting to the Database</a><br />
     
<a href="#support">SOCI Feature Support</a><br />
<div class="navigation-indented">
    <a href="#dynamic">Dynamic Binding</a><br />
    <a href="#bindingbyname">Binding by Name</a><br />
    <a href="#bulk">Bulk Operations</a><br />
    <a href="#transactions">Transactions</a><br />
    <a href="#blob">BLOB Data Type</a><br />
    <a href="#rowid">RowID Data Type</a><br />
    <a href="#nested">Nested Statements</a><br />
    <a href="#procedures">Stored Procedures</a><br />
</div>
  <a href="#native">Accessing the Native Database API</a><br />
  <a href="#extensions">Backend-specific Extensions</a><br />
  <a href="#options">Configuration options</a><br />
</div>

<h3 id="prerequisites">Prerequisites</h3>
<h4 id="versions">Supported Versions</h4>

<p>The SOCI PostgreSQL backend is supported for use with PostgreSQL >= 7.3, although versions older than 8.0 will suffer from limited feature support. See below for details.</p>

<h4 id="tested">Tested Platforms</h4>

<table border="1" cellpadding="5" cellspacing="0">
<tbody>
<tr><th>PostgreSQL version</th><th>Operating System</th><th>Compiler</th></tr>
<tr><td>9.0</td><td>Mac OS X 10.6.6</td><td>g++ 4.2</td></tr>
<tr><td>8.4</td><td>FreeBSD 8.2</td><td>g++ 4.1</td></tr>
<tr><td>8.4</td><td>Debian 6</td><td>g++ 4.3</td></tr>
<tr><td>8.4</td><td>RedHat 5</td><td>g++ 4.3</td></tr>
</tbody>
</table>

<h4 id="required">Required Client Libraries</h4>

<p>The SOCI PostgreSQL backend requires PostgreSQL's <code>libpq</code> client library.</p>
<p>Note that the SOCI library itself depends also on <code>libdl</code>, so the minimum set of libraries needed to compile a basic client program is:</p>
<pre class="example">
-lsoci_core -lsoci_postgresql -ldl -lpq
</pre>

<h4 id="connecting">Connecting to the Database</h4>

<p>To establish a connection to the PostgreSQL database, create a <code>session</code> object
using the <code>postgresql</code> backend factory together with a connection string:</p>

<pre class="example">
session sql(postgresql, "dbname=mydatabase");

// or:
session sql("postgresql", "dbname=mydatabase");

// or:
session sql("postgresql://dbname=mydatabase");
</pre>

<p>The set of parameters used in the connection string for PostgreSQL is the same as accepted by the <code><a href="http://www.postgresql.org/docs/8.3/interactive/libpq.html#LIBPQ-CONNECT">PQconnectdb</a></code> function from the <code>libpq</code> library.</p>

<p>Once you have created a <code>session</code> object as shown above, you can use it to access the database, for example:</p>
<pre class="example">
int count;
sql &lt;&lt; "select count(*) from invoices", into(count);
</pre>

<p>(See the <a href="../basics.html">SOCI basics</a> and <a href="../exchange.html">exchanging data</a> documentation for general information on using the <code>session</code> class.)</p>

<h3 id="support">SOCI Feature Support</h3>

<h4 id="dynamic">Dynamic Binding</h4>

<p>The PostgreSQL backend supports the use of the SOCI <code>row</code> class, which facilitates retrieval of data whose type is not known at compile time.</p>

<p>When calling <code>row::get&lt;T&gt;()</code>, the type you should pass as <code>T</code> depends upon the underlying database type.<br/> For the PostgreSQL backend, this type mapping is:</p>

<table border="1" cellpadding="5" cellspacing="0">
  <tbody>
    <tr>
      <th>PostgreSQL Data Type</th>
      <th>SOCI Data Type</th>
      <th><code>row::get&lt;T&gt;</code> specializations</th>
    </tr>
    <tr>
      <td>numeric, real, double</td>
      <td><code>dt_double</code></td>
      <td><code>double</code></td>
    </tr>
    <tr>
      <td>boolean, smallint, integer</td>
      <td><code>dt_integer</code></td>
      <td><code>int</code></td>
    </tr>
    <tr>
      <td>int8</td>
      <td><code>dt_long_long</code></td>
      <td><code>long long</code></td>
    </tr>
    <tr>
      <td>oid</td>
      <td><code>dt_integer</code></td>
      <td><code>unsigned long</code></td>
    </tr>
    <tr>
      <td>char, varchar, text, cstring, bpchar</td>
      <td><code>dt_string</code></td>
      <td><code>std::string</code></td>
    </tr>
    <tr>
      <td>abstime, reltime, date, time, timestamp, timestamptz, timetz</td>
      <td><code>dt_date</code></td>
      <td><code>std::tm</code><code></code></td>
    </tr>
  </tbody>
</table>

<p>(See the <a href="../exchange.html#dynamic">dynamic resultset binding</a> documentation for general information on using the <code>row</code> class.)</p>

<h4 id="bindingbyname">Binding by Name</h4>

<p>In addition to <a href="../exchange.html#bind_position">binding by position</a>, the PostgreSQL backend supports <a href="../exchange.html#bind_name">binding by name</a>, via an overload of the <code>use()</code> function:</p>

<pre class="example">
int id = 7;
sql &lt;&lt; "select name from person where id = :id", use(id, "id")
</pre>

<p>Apart from the portable "colon-name" syntax above, which is achieved by rewriting the query string, the backend also supports the PostgreSQL native numbered syntax:</p>

<pre class="example">
int i = 7;
int j = 8;
sql &lt;&lt; "insert into t(x, y) values($1, $2)", use(i), use(j);
</pre>

<p>The use of native syntax is not recommended, but can be nevertheless imposed by switching off the query rewriting. This can be achieved by defining the macro <code>SOCI_POSTGRESQL_NOBINDBYNAME</code> and it is actually necessary for PostgreSQL 7.3, in which case binding of use elements is not supported at all. See the <a href="#options">Configuration options</a> section for details.</p>

<h4 id="bulk">Bulk Operations</h4>

<p>The PostgreSQL backend has full support for SOCI's <a href="../statements.html#bulk">bulk operations</a> interface.</p>

<h4 id="transactions">Transactions</h4>

<p><a href="../statements.html#transactions">Transactions</a> are also fully supported by the PostgreSQL backend.</p>

<h4 id="blob">blob Data Type</h4>

<p>The PostgreSQL backend supports working with data stored in columns of type Blob, via SOCI's <a href="../exchange.html#blob"><code>blob</code></a> class with the exception that trimming is not supported.</p>

<h4 id="rowid">rowid Data Type</h4>

<p>The concept of row identifier (OID in PostgreSQL) is supported via SOCI's <a href="../reference.html#rowid">rowid</a> class.</p>

<h4 id="nested">Nested Statements</h4>

<p>Nested statements are not supported by PostgreSQL backend.</p>

<h4 id="procedures">Stored Procedures</h4>

<p>PostgreSQL stored procedures can be executed by using SOCI's <a href="../statements.html#procedures">procedure</a> class.</p>

<h3 id="native">Acessing the native database API</h3>

<p>SOCI provides access to underlying datbabase APIs via several <code>get_backend()</code> functions, as described in the <a href="../beyond.html">beyond SOCI</a> documentation.</p>

<p>The PostgreSQL backend provides the following concrete classes for navite API access:</p>

<table border="1" cellpadding="5" cellspacing="0">
  <tbody>
    <tr>
      <th>Accessor Function</th>
      <th>Concrete Class</th>
    </tr>
    <tr>
      <td><code>session_backend * session::get_backend()</code></td>
      <td><code>postgresql_session_backend</code></td>
    </tr>
    <tr>
      <td><code>statement_backend * statement::get_backend()</code></td>
      <td><code>postgresql_statement_backend</code></td>
    </tr>
    <tr>
      <td><code>blob_backend * blob::get_backend()</code></td>
      <td><code>postgresql_blob_backend</code></td>
    </tr>
    <tr>
      <td><code>rowid_backend * rowid::get_backend()</code></td>
      <td><code>postgresql_rowid_backend</code></td>
    </tr>
  </tbody>
</table>


<h3 id="extensions">Backend-specific extensions</h3>

<p>None.</p>

<h3 id="options">Configuration options</h3>

<p>To support older PostgreSQL versions, the following configuration macros are recognized:</p>
<ul>
<li><code>SOCI_POSTGRESQL_NOBINDBYNAME</code> - switches off the query rewriting.</li>
<li><code>SOCI_POSTGRESQL_NOPARAMS</code> - disables support for parameterized queries (binding of use elements), automatically imposes also the <code>SOCI_POSTGRESQL_NOBINDBYNAME</code> macro. It is necessary for PostgreSQL 7.3.</li>
<li><code>SOCI_POSTGRESQL_NOPREPARE</code> - disables support for separate query preparation, which in this backend is significant only in terms of optimization. It is necessary for PostgreSQL 7.3 and 7.4.</li>
</ul>


<p class="copyright">Copyright &copy; 2004-2008 Maciej Sobczak, Stephen Hutton</p>
</body>
</html>
